--创建外部表
create external table if not exists dongfeng.navinfo_stage_obd (
        protocolType                                      INT                 COMMENT 'OBD诊断协议',
        milType                                           INT                 COMMENT 'MIL状态',
        diagnosticSupportStatus                           INT                 COMMENT '诊断支持状态',
        diagnosticReadinessStatus                         INT                 COMMENT '诊断就绪状态',
        calid                                             STRING              COMMENT '校准标识符',
        vin                                               STRING              COMMENT 'vin',
        calibrationIdentifyNO                             STRING              COMMENT '软件标定识别号',
        cvn                                               STRING              COMMENT '标定验证码',
        iupr                                              STRING              COMMENT 'IUPR',
        faultCount                                        INT                 COMMENT '故障码总数',
        faultCodes                                        STRING              COMMENT '故障码信息列表',
        isPatch                                           BOOLEAN             COMMENT '是否补传数据',
        gpsTime                                           BIGINT              COMMENT 'GPS时间',
        encrypt                                           INT                 COMMENT '数据加密方式',
        terminalVersion                                   INT                 COMMENT '终端软件版本号',
        terminalId                                        BIGINT              COMMENT '终端号',
        serialNumber                                      BIGINT              COMMENT '序列号',
        rLength                                           INT                 COMMENT 'R值长度',
        sLength                                           INT                 COMMENT 'S值长度',
        rValue                                            STRING              COMMENT 'R值BASE64编码',
        sValue                                            STRING              COMMENT 'S值BASE64编码',
        part_time                                         INT                 COMMENT '分区时间'
) row format delimited fields terminated by '\001' STORED AS TEXTFILE location '/user/data/dongfeng/stage_import_obd';

--创建目标表
create table if not exists dongfeng.navinfo_parquet_obd(
        protocolType                                      INT                 COMMENT 'OBD诊断协议',
        milType                                           INT                 COMMENT 'MIL状态',
        diagnosticSupportStatus                           INT                 COMMENT '诊断支持状态',
        diagnosticReadinessStatus                         INT                 COMMENT '诊断就绪状态',
        calid                                             STRING              COMMENT '校准标识符',
        vin                                               STRING              COMMENT 'vin',
        calibrationIdentifyNO                             STRING              COMMENT '软件标定识别号',
        cvn                                               STRING              COMMENT '标定验证码',
        iupr                                              STRING              COMMENT 'IUPR',
        faultCount                                        INT                 COMMENT '故障码总数',
        faultCodes                                        STRING              COMMENT '故障码信息列表',
        isPatch                                           BOOLEAN             COMMENT '是否补传数据',
        gpsTime                                           BIGINT              COMMENT 'GPS时间',
        encrypt                                           INT                 COMMENT '数据加密方式',
        terminalVersion                                   INT                 COMMENT '终端软件版本号',
        terminalId                                        BIGINT              COMMENT '终端号',
        serialNumber                                      BIGINT              COMMENT '序列号',
        rLength                                           INT                 COMMENT 'R值长度',
        sLength                                           INT                 COMMENT 'S值长度',
        rValue                                            STRING              COMMENT 'R值BASE64编码',
        sValue                                            STRING              COMMENT 'S值BASE64编码',
        create_time                                       STRING              COMMENT '数据创建时间'
) partitioned by (part_time int) stored as Parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
